import pandas as pd
import mysql.connector
from mysql.connector import Error
from datetime import datetime

def create_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")
    return connection

def execute_query(connection, query, data=None):
    cursor = connection.cursor()
    try:
        if data:
            cursor.executemany(query, data)
        else:
            cursor.execute(query)
        connection.commit()
        print("Query executed successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

# 创建连接
connection = create_connection("localhost", "root", "123456", "novels")  # 替换你的数据库信息

# 创建表（如果不存在）
create_table_query = '''
CREATE TABLE IF NOT EXISTS collection_of_novels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    serial_number INT,                      -- 序号
    moment_of_excellence DATETIME,           -- 精品时刻
    category VARCHAR(255),                  -- 分类
    title VARCHAR(255),                     -- 书名
    author VARCHAR(255),                    -- 作者
    author_level VARCHAR(255),              -- 作者等级
    data_refresh_time DATETIME,             -- 数据刷新时间
    favorites_count INT,                    -- 收藏数
    real_fans_count INT,                    -- 真实粉丝数
    word_count BIGINT,                       -- 字数
    status VARCHAR(255),                    -- 状态
    last_update_time DATETIME               -- 最后更新时间
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
'''

execute_query(connection, create_table_query)

# 读取Excel文件
file_path = r'D:\downloads\novel.xlsx'
df = pd.read_excel(file_path)

# 确保所有列都存在且顺序正确
column_names = ['serial_number', 'moment_of_excellence', 'category', 'title', 'author', 'author_level', 'data_refresh_time', 'favorites_count', 'real_fans_count', 'word_count', 'status', 'last_update_time']
df.columns = column_names  # 设置列名以匹配表结构

# 数据清洗：移除或填充 NaN 值
# 对于整数和浮点数类型的列，使用0填充；对于字符串类型的列，使用空字符串填充；对于日期时间类型的列，使用None填充。
df['serial_number'] = df['serial_number'].fillna(0).astype(int)
df['moment_of_excellence'] = pd.to_datetime(df['moment_of_excellence'], errors='coerce')
df['category'] = df['category'].fillna('')
df['title'] = df['title'].fillna('')
df['author'] = df['author'].fillna('')
df['author_level'] = df['author_level'].fillna('')
df['data_refresh_time'] = pd.to_datetime(df['data_refresh_time'], errors='coerce')
df['favorites_count'] = df['favorites_count'].fillna(0).astype(int)
df['real_fans_count'] = df['real_fans_count'].fillna(0).astype(int)
df['word_count'] = df['word_count'].fillna(0).astype(int)
df['status'] = df['status'].fillna('')
df['last_update_time'] = pd.to_datetime(df['last_update_time'], errors='coerce')

# 将DataFrame转换为列表元组，并过滤掉任何包含NaN的行
data_to_insert = df.dropna().values.tolist()

# 插入数据
insert_data_query = '''
INSERT INTO collection_of_novels (serial_number, moment_of_excellence, category, title, author, author_level, data_refresh_time, favorites_count, real_fans_count, word_count, status, last_update_time) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''

execute_query(connection, insert_data_query, data_to_insert)

# 关闭连接
if connection.is_connected():
    connection.close()
    print("The MySQL connection is closed")